import pymysql


class DBTools(object):
    # 建立连接 - 静态方法 类名直接调用
    @staticmethod
    def __create_conn():
        # 指定 ip、port、用户名、密码、数据库、返回链接
        conn = pymysql.connect(host='211.103.136.244', port=7061, user='student',
                               password='iHRM_student_2022', database='ihrm', charset='utf8')
        return conn

    # 查询方法
    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None
        res = None
        try:
            # 获取连接、创建游标、执行sql、提结果集、返回一条结果
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print('查询sql失败：', err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_many(sql, n):
        cursor = None
        conn = None
        res = None
        # 获取连接、创建游标、执行sql、提结果集、返回多条结果
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)
        except Exception as err:
            print('查询多条失败：', err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_all(sql):
        cursor = None
        conn = None
        res = None
        # 获取连接、创建游标、执行sql、提结果集、返回多条结果
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as err:
            print('查询全部失败：', err)
        finally:
            cursor.close()
            conn.close()
            return res

    # 增删改方法
    @staticmethod
    def db_idu(sql):
        # 获取连接、创建游标、执行sql、提结果集、返回多条结果
        conn = None
        cursor = None
        # 创建连接
        try:
            conn = pymysql.connect(host='211.103.136.244', port=7061, user='student',
                                   password='iHRM_student_2022', database='ihrm', charset='utf8')
            # 创建游标
            cursor = conn.cursor()
            # 执行 增加 SQL语句
            res = cursor.execute(sql)
            print('affected rows:', res)
            # 执行成功，提交事务  commit()
            conn.commit()
        except Exception as err:
            # 执行失败 回滚操作
            conn.rollback()
            print('增删改失败：', err)
        finally:
            # 5. 关闭游标
            cursor.close()
            # 6. 关闭连接
            conn.close()


if __name__ == '__main__':
    res = DBTools.query_one("select * from t_book;")
    print(res)
    res1 = DBTools.query_many("select * from t_book;", 3)
    print(res1)
    res2 = DBTools.query_all("select * from t_book;")
    print(res2)
    DBTools.db_idu("insert into t_book(id,title,pub_date) VALUES(3,'金色传说','1986-01-01');")
    DBTools.db_idu("UPDATE t_book set `read`= `read` +1 where title = '金色传说';")
    DBTools.db_idu("delete from t_book where title='那些年';")
